package mytest.excel;

import java.time.*;
import java.time.format.DateTimeFormatter;

public class Test1 {

    public static void main(String[] args) throws Exception {

        String dateStr = "2024-09-26";

        DateTimeFormatter formatter = DateTimeFormatter.ISO_LOCAL_DATE;

        // 解析日期字符串
        LocalDate date = LocalDate.parse(dateStr, formatter);
        int year = date.getYear();
        int month = date.getMonthValue();
        System.out.println(year + " --- " + month);
        String tableNameSuffix = String.format("%d%02d", year, month);
        System.out.println(tableNameSuffix);

        // 设置时间为当天的开始（00:00:00）和结束（23:59:59）
        LocalDateTime startOfDay = date.atStartOfDay();
        LocalDateTime endOfDay = date.atTime(23, 59, 59);

        // 转换为UTC时间戳
        ZoneId nigeriaTimeZone = ZoneId.of("Africa/Lagos");
        Instant startOfDayInstant = startOfDay.atZone(nigeriaTimeZone).toInstant();
        Instant endOfDayInstant = endOfDay.atZone(nigeriaTimeZone).toInstant();

        // 获取秒级时间戳 10位数
        String startOfDayTimestampSec = String.valueOf(startOfDayInstant.getEpochSecond());
        String endOfDayTimestampSec = String.valueOf(endOfDayInstant.getEpochSecond());

        // 获取毫秒级时间戳 13位数
        String startOfDayTimestampMs = String.valueOf(startOfDayInstant.toEpochMilli());
        String endOfDayTimestampMs = String.valueOf(endOfDayInstant.toEpochMilli());

        System.out.println("2024-09-26 10位数开始:" + startOfDayTimestampSec);
        System.out.println("2024-09-26 10位数结束:" + endOfDayTimestampSec);
        System.out.println("2024-09-26 13位数开始:" + startOfDayTimestampMs);
        System.out.println("2024-09-26 13位数结束:" + endOfDayTimestampMs);

        String tableName_1 = "t_call_log_202409";
        String tableName_2 = "log_dial_task_assign_202409";

        String sql = "INSERT INTO t_user_task_call_report\n" +
                "select \n" +
                " t.work_date as work_day,\n" +
                " t.user_gid as user_id,\n" +
                " t.username as user_name,\n" +
                " t.customer_group as customer_group,\n" +
                " count(distinct t.dial_task_gid) as task_count,\n" +
                " count(DISTINCT CASE WHEN t.answer_time IS NOT NULL THEN t.dial_task_gid END) AS task_call_count,\n" +
                " count(DISTINCT CASE WHEN t.answer_time > 0 THEN t.dial_task_gid END) AS task_connect_count,\n" +
                " count(t.answer_time) as call_count,\n" +
                " count(CASE WHEN t.answer_time > 0 THEN 1 END) as connect_count,\n" +
                " SUM(t.answer_time) as connect_total_time\n" +
                "from \n" +
                "(select\n" +
                "  a.gid,\n" +
                "  a.dial_task_gid, \n" +
                "  a.user_gid, \n" +
                "  b.username, \n" +
                "  '%s' as work_date, \n" +
                "  c.user_classify，\n" +
                "  c.cus_cat, \n" +
                "  c.phone， \n" +
                "  c.answer_time,\n" +
                "        d.customer_group\n" +
                "from (\n" +
                "  select gid,dial_task_gid,create_time,end_time,user_gid \n" +
                "  from %s \n" +
                "  where (end_time is null and create_time <= '%s' ) or (create_time <= '%s'  and end_time >= '%s') \n" +
                ") a \n" +
                "left join t_user b on a.user_gid = b.user_id\n" +
                "left join (\n" +
                "  select task_id,user_id,user_classify,cus_cat,phone,answer_time\n" +
                "  from %s\n" +
                "  where start_time >= '%s' and end_time <= '%s'\n" +
                ") c on a.dial_task_gid = c.task_id and a.user_gid=c.user_id\n" +
                "left join t_cus_cat_group d\n" +
                "on c.user_classify = d.user_classify and c.cus_cat = d.cus_cat) t\n" +
                "GROUP BY t.work_date,t.user_id,t.username,t.customer_group;";

        String PR_TABLE_1 = "log_dial_task_assign_";
        String PR_TABLE_2 = "t_call_log_";

        String format = String.format(sql, date, PR_TABLE_1 + tableNameSuffix , endOfDayTimestampSec, endOfDayTimestampSec, startOfDayTimestampSec, PR_TABLE_2 + tableNameSuffix, startOfDayTimestampMs, endOfDayTimestampMs);
        System.out.println(format);
    }

}
